package com.sponge.utils;

import com.sponge.constant.DatabaseConstant;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseUtils {

    private String tinyint = " tinyint(2),";
    private String varchar = " varchar(255),";
    private String bigint = " bigint,";
    private String int_sql = " int,";
    private Statement statement = null;
    private Connection connection = null;

    public void createDatabase(){
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            StringBuffer dropDatabase = new StringBuffer();
            dropDatabase.append("drop database if exists ").append(DatabaseConstant.DATABASE).append(";");
            statement.execute(dropDatabase.toString());

            StringBuffer createDatabase = new StringBuffer();
            createDatabase.append("create database ").append(DatabaseConstant.DATABASE).append(";");
            statement.execute(createDatabase.toString());

            StringBuffer useDatabase = new StringBuffer();
            useDatabase.append("use ").append(DatabaseConstant.DATABASE).append(";");
            statement.execute(useDatabase.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createTable(){

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            StringBuffer dropTableCreditEntity = new StringBuffer();
            dropTableCreditEntity.append("drop table if exists ").append(DatabaseConstant.TABLE_CREDIT_ENTITY).append(";");
            statement.execute(dropTableCreditEntity.toString());

            StringBuffer createTableCreditEntity = new StringBuffer();
            createTableCreditEntity.append("create table ").append(DatabaseConstant.TABLE_CREDIT_ENTITY).append("(").append(DatabaseConstant.ENTITY_INDEX).append(" bigint auto_increment primary key,").append(DatabaseConstant.ENTITY_ID).append(" bigint unique,").append(DatabaseConstant.ENTITY_CATEGORY).append(int_sql).append(DatabaseConstant.ENTITY_IDENTITY_NUMBER).append(varchar).append(DatabaseConstant.ENTITY_NAME).append(varchar).append(DatabaseConstant.PASSWORD).append(varchar).append(DatabaseConstant.SECONDARY_CATEGORY).append(int_sql).append(DatabaseConstant.THIRD_CATEGORY).append(int_sql).append(DatabaseConstant.ENTITY_STATUS).append(int_sql).append(DatabaseConstant.CREDIT_VALUE).append(int_sql).append(DatabaseConstant.ADD_TIME).append(bigint).append(DatabaseConstant.UPDATE_TIME).append(" bigint);");
            System.out.println(createTableCreditEntity.toString());
            statement.execute(createTableCreditEntity.toString());

            StringBuffer dropTableCredentialInfo = new StringBuffer();
            dropTableCredentialInfo.append("drop table if exists ").append(DatabaseConstant.TABLE_CREDENTIAL_INFO).append(";");
            statement.execute(dropTableCredentialInfo.toString());

            StringBuffer createTableCredentialInfo = new StringBuffer();
            createTableCredentialInfo.append("create table ").append(DatabaseConstant.TABLE_CREDENTIAL_INFO).append("(").append(DatabaseConstant.CREDENTIAL_INDEX).append(" bigint auto_increment primary key,").append(DatabaseConstant.ENTITY_ID).append(bigint).append(DatabaseConstant.CONTRACT_PASSWORD).append(varchar).append(DatabaseConstant.ADDRESS).append(varchar).append(DatabaseConstant.PRIVATE_KEY).append(varchar).append(DatabaseConstant.PUBLIC_KEY).append(varchar).append(DatabaseConstant.KEYSTORE_FILE).append(varchar).append(DatabaseConstant.CREDENTIAL_STATUS).append(int_sql).append(DatabaseConstant.ADD_TIME).append(bigint).append(DatabaseConstant.UPDATE_TIME).append(" bigint);");
            System.out.println(createTableCredentialInfo.toString());
            statement.execute(createTableCredentialInfo.toString());

            StringBuffer dropTableCreditRecorder = new StringBuffer();
            dropTableCreditRecorder.append("drop table if exists ").append(DatabaseConstant.TABLE_CREDIT_RECORDER).append(";");
            statement.execute(dropTableCreditRecorder.toString());

            StringBuffer createTableCreditRecorder = new StringBuffer();
            createTableCreditRecorder.append("create table ").append(DatabaseConstant.TABLE_CREDIT_RECORDER).append("(").append(DatabaseConstant.RECORDER_INDEX).append(" bigint auto_increment primary key,").append(DatabaseConstant.RECORDER_HASHCODE).append(varchar).append(DatabaseConstant.AGENCY_ID).append(bigint).append(DatabaseConstant.ENTITY_ID).append(bigint).append(DatabaseConstant.CREDIT_VALUE).append(int_sql).append(DatabaseConstant.DESCRIPTION).append(varchar).append(DatabaseConstant.RECORDER_STATUS).append(int_sql).append(DatabaseConstant.ADD_TIME).append(bigint).append(DatabaseConstant.UPDATE_TIME).append(" bigint);");
            System.out.println(createTableCreditRecorder.toString());
            statement.execute(createTableCreditRecorder.toString());




        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//
//    public static void main(String[] args) {
//        DatabaseUtils databaseUtils = new DatabaseUtils();
//        databaseUtils.createTable();
//    }

}
